<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <title>MySQL存储过程与函数  &middot; sitename</title>
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="HandheldFriendly" content="True">
<meta name="MobileOptimized" content="320">
<meta name="viewport" content="width=device-width, initial-scale=1">


<meta name="description" content="MySQL存储过程与函数 description" />

<meta name="keywords" content="one, two, ">


<meta property="og:title" content="MySQL存储过程与函数  &middot; sitename ">
<meta property="og:site_name" content="sitename"/>
<meta property="og:url" content="https://lvzongcheng.gitee.io/sql/mysql%E5%82%A8%E5%AD%98%E8%BF%87%E7%A8%8B%E5%87%BD%E6%95%B0/" />
<meta property="og:locale" content="en-EN">


<meta property="og:type" content="article" />
<meta property="og:description" content="MySQL存储过程与函数 description"/>
<meta property="og:article:published_time" content="2017-05-13T00:00:00Z" />
<meta property="og:article:modified_time" content="2017-05-13T00:00:00Z" />

  
    
<meta property="og:article:tag" content="one">
    
<meta property="og:article:tag" content="two">
    
  

  

<script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Article",
    "headline": "MySQL存储过程与函数",
    "author": {
      "@type": "Person",
      "name": "LvZongCheng"
    },
    "datePublished": "2017-05-13",
    "description": "MySQL存储过程与函数 description",
    "wordCount":  422 
  }
</script>



<link rel="canonical" href="https://lvzongcheng.gitee.io/sql/mysql%E5%82%A8%E5%AD%98%E8%BF%87%E7%A8%8B%E5%87%BD%E6%95%B0/" />

<link rel="apple-touch-icon-precomposed" sizes="144x144" href="https://lvzongcheng.gitee.io/touch-icon-144-precomposed.png">
<link href="https://lvzongcheng.gitee.io/favicon.png" rel="icon">

<meta name="generator" content="Hugo 0.60.0" />

  <!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->

<link href='https://fonts.googleapis.com/css?family=Merriweather:300%7CRaleway%7COpen+Sans' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="/css/font-awesome.min.css">
<link rel="stylesheet" href="/css/style.css">
<link rel="stylesheet" href="/css/highlight/default.css">

  
  
	<script>
	  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
	  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
	  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
	  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

	  ga('create', 'Your Google Analytics tracking code', 'auto');
	  ga('send', 'pageview');

	</script>

</head>
<body>
  <main id="main-wrapper" class="container main_wrapper has-sidebar">
    <header id="main-header" class="container main_header">
  <div class="container brand">
  <div class="container title h1-like">
  <a class="baselink" href="https://lvzongcheng.gitee.io">
  LvZongCheng

</a>

</div>

  
<div class="container topline">
  
  淡定 执著 进取 自律


</div>


</div>

  <nav class="container nav primary no-print">
  

<a class="homelink" href="https://lvzongcheng.gitee.io">home</a>


  
<a href="https://lvzongcheng.gitee.io/framework" title="Show list of Frameworks">Frameworks</a>

<a href="https://lvzongcheng.gitee.io/java" title="Show list of Java">Java</a>

<a href="https://lvzongcheng.gitee.io/linux" title="Show list of Linux">Linux</a>

<a href="https://lvzongcheng.gitee.io/redis" title="Show list of Redis">Redis</a>

<a href="https://lvzongcheng.gitee.io/sql" title="Show list of SQL">SQL</a>

<a href="https://lvzongcheng.gitee.io/Spring" title="Show list of Spring">Spring</a>

<a href="https://lvzongcheng.gitee.io/tools" title="Show list of Tools">Tools</a>

<a href="https://lvzongcheng.gitee.io/webpage" title="Show list of WebPage">WebPage</a>

<a href="https://lvzongcheng.gitee.io/information" title="Show list of information">information</a>

<a href="https://lvzongcheng.gitee.io/network" title="Show list of network">network</a>

<a href="https://lvzongcheng.gitee.io/%E5%BE%AE%E6%9C%8D%E5%8A%A1" title="Show list of 微服务">微服务</a>

<a href="https://lvzongcheng.gitee.io/%E6%80%BB%E7%BB%93" title="Show list of 总结">总结</a>

<a href="https://lvzongcheng.gitee.io/%E6%9C%BA%E5%99%A8%E8%87%AA%E5%8A%A8%E5%8C%96" title="Show list of 机器自动化">机器自动化</a>

<a href="https://lvzongcheng.gitee.io/%E6%B1%87%E7%BC%96" title="Show list of 汇编">汇编</a>

<a href="https://lvzongcheng.gitee.io/%E8%AE%B0%E4%BA%8B%E6%9C%AC" title="Show list of 记事本">记事本</a>


</nav>

<div class="container nav secondary no-print">
  
<a id="contact-link-email" class="contact_link" rel="me" aria-label="Email" href="mailto:Lyc19880405@163.com">
  <span class="fa fa-envelope-square"></span></a>



<a id="contact-link-github" class="contact_link" rel="me" aria-label="Github" href="https://github.com/enten/hugo-boilerplate">
  <span class="fa fa-github-square"></span></a>




 


















</div>


  

</header>


<article id="main-content" class="container main_content single">
  <header class="container hat">
  <h1>MySQL存储过程与函数
</h1>

</header>

  <div class="container content">
  <hr>
<h3 id="heading">总结</h3>
<p>1.在遍历游标中使用 查询数据使用 SELECT INTO 会导致提前中断游标
原因：因为当select· into赋值结果为null的时候，会自动修改循环触底的标志
解决办法</p>
<ul>
<li>采用set 赋值语句，比如上面的语句可以改成</li>
</ul>
<pre><code> set var_last_entry_date = (select entry_date from employee 
where department_id = var_department_id order by entry_date desc limit 0,1);  
</code></pre><ul>
<li>采用select count 语句，比如</li>
</ul>
<pre><code>select if (count(entry_date) &gt; 0, entry_date, '') into var_last_entry_date from employee 
where department_id = var_department_id order by entry_date desc limit 0,1;  
</code></pre><p>采用此方法的话，下面的判断条件if var_last_entry_date is not null 就需要与空字符串进行比较了，即改成if var_last_entry_date != '&rsquo; then。</p>
<h3 id="heading1">异常捕获</h3>
<h5 id="1-">1 异常定义</h5>
<h6 id="11-">1.1 语法</h6>
<p>DECLARE condition_name CONDITION FOR [condition_type];</p>
<h6 id="12-">1.2 说明</h6>
<p>condition_name参数表示异常的名称； 
condition_type参数表示条件的类型，condition_type由SQLSTATE [VALUE] sqlstate_value|mysql_error_code组成：</p>
<pre><code>  sqlstate_value和mysql_error_code都可以表示MySQL的错误；
  sqlstate_value为长度为5的字符串类型的错误代码；
  mysql_error_code为数值类型错误代码； 
</code></pre>
<h6 id="13-">1.3 示例</h6>
<p>定义“ERROR 1148(42000)”错误，名称为command_not_allowed。可以有以下两种方法：</p>
<pre><code>//方法一：使用sqlstate_value 
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000′; 
//方法二：使用mysql_error_code 
DECLARE command_not_allowed CONDITION FOR 1148; 
</code></pre><h5 id="2-">2 自定义异常处理</h5>
<h6 id="21-">2.1 异常处理语法</h6>
<p>DECLARE handler_type HANDLER FOR condition_value [,&hellip;] sp_statement</p>
<h6 id="22-">2.2 参数说明</h6>
<p>handler_type: CONTINUE|EXIT|UNDO</p>
<p>handler_type为错误处理方式，参数为3个值之一；
CONTINUE表示遇到错误不处理，继续执行；
EXIT表示遇到错误时马上退出；
UNDO表示遇到错误后撤回之前的操作，MySQL暂不支持回滚操作；</p>
<p>condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code</p>
<p>condition_value表示错误类型；
SQLSTATE [VALUE] sqlstate_value为包含5个字符的字符串错误值；
condition_name表示DECLARE CONDITION定义的错误条件名称；
SQLWARNING匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
mysql_error_code匹配数值类型错误代码；</p>
<h6 id="23-">2.3 异常捕获方法</h6>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#f92672">/</span><span style="color:#f92672">/</span><span style="color:#960050;background-color:#1e0010">方</span><span style="color:#960050;background-color:#1e0010">法</span><span style="color:#960050;background-color:#1e0010">一</span><span style="color:#960050;background-color:#1e0010">：</span><span style="color:#960050;background-color:#1e0010">捕</span><span style="color:#960050;background-color:#1e0010">获</span>sqlstate_value异常 
<span style="color:#f92672">/</span><span style="color:#f92672">/</span><span style="color:#960050;background-color:#1e0010">这</span><span style="color:#960050;background-color:#1e0010">种</span><span style="color:#960050;background-color:#1e0010">方</span><span style="color:#960050;background-color:#1e0010">法</span><span style="color:#960050;background-color:#1e0010">是</span><span style="color:#960050;background-color:#1e0010">捕</span><span style="color:#960050;background-color:#1e0010">获</span>sqlstate_value值<span style="color:#960050;background-color:#1e0010">。</span><span style="color:#960050;background-color:#1e0010">如</span><span style="color:#960050;background-color:#1e0010">果</span><span style="color:#960050;background-color:#1e0010">遇</span><span style="color:#960050;background-color:#1e0010">到</span>sqlstate_value值为<span style="color:#960050;background-color:#1e0010">”</span><span style="color:#ae81ff">42</span>S02<span style="color:#960050;background-color:#1e0010">″</span><span style="color:#960050;background-color:#1e0010">，</span><span style="color:#960050;background-color:#1e0010">执</span><span style="color:#960050;background-color:#1e0010">行</span>CONTINUE操作<span style="color:#960050;background-color:#1e0010">，</span><span style="color:#960050;background-color:#1e0010">并</span><span style="color:#960050;background-color:#1e0010">输</span><span style="color:#960050;background-color:#1e0010">出</span><span style="color:#960050;background-color:#1e0010">”</span>NO_SUCH_TABLE<span style="color:#960050;background-color:#1e0010">”</span><span style="color:#960050;background-color:#1e0010">信</span><span style="color:#960050;background-color:#1e0010">息</span> 
<span style="color:#66d9ef">DECLARE</span> <span style="color:#66d9ef">CONTINUE</span> <span style="color:#66d9ef">HANDLER</span> <span style="color:#66d9ef">FOR</span> <span style="color:#66d9ef">SQLSTATE</span> <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">42S02′ SET @info=</span><span style="color:#e6db74">&#39;</span>NO_SUCH_TABLE<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">; 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74">//方法二：捕获mysql_error_code异常 
</span><span style="color:#e6db74">//这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出”NO_SUCH_TABLE”信息； 
</span><span style="color:#e6db74">DECLARE CONTINUE HANDLER FOR 1146 SET @info=</span><span style="color:#e6db74">&#39;</span>NO_SUCH_TABLE<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">; 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74">//方法三：先定义条件，然后捕获异常 
</span><span style="color:#e6db74">DECLARE no_such_table CONDITION FOR 1146; 
</span><span style="color:#e6db74">DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=</span><span style="color:#e6db74">&#39;</span>NO_SUCH_TABLE<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">;  
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74">//方法四：使用SQLWARNING捕获异常 
</span><span style="color:#e6db74">DECLARE EXIT HANDLER FOR SQLWARNING SET @info=</span><span style="color:#e6db74">&#39;</span>ERROR<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">;  
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74">//方法五：使用NOT FOUND捕获异常 
</span><span style="color:#e6db74">DECLARE EXIT HANDLER FOR NOT FOUND SET @info=</span><span style="color:#e6db74">&#39;</span>NO_SUCH_TABLE<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">;
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74"> 
</span><span style="color:#e6db74">//方法六：使用SQLEXCEPTION捕获异常 
</span><span style="color:#e6db74">DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=</span><span style="color:#e6db74">&#39;</span>ERROR<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">;
</span></code></pre></div><hr>
<h3 id="heading2">日志输出</h3>
<ul>
<li>直接用select 打印输出</li>
</ul>
<pre><code>SELECT 'Comment';
</code></pre><ul>
<li>用concat连接变量输出</li>
</ul>
<pre><code>declare myvar INT default 0;
SET myvar = 5;
SELECT concat('myvar is ', myvar);
</code></pre><p>输出： myvar is 5</p>
<ul>
<li>格外创建一个有一列文本列的表，然后往里面塞信息</li>
</ul>
<pre><code>declare myvar INT default 0;
SET myvar = 5;
insert into tmptable select concat('myvar is ', myvar);
</code></pre><ul>
<li>将上面的sql语句封装成一个存储过程log，以后要用的话就直接调用下面的语句就可以了</li>
</ul>
<pre><code>CALL log(concat('the value is', myvar));
</code></pre><ul>
<li>直接输出到一个文本里面</li>
</ul>
<pre><code>select &quot;penguin&quot; as log into outfile '/tmp/result.txt';
</code></pre><p>这个命令会有严格的限制，只能将输出文本放在本地，然后给予其创建和写的权限
一旦输出了一个文本，无法重写，这样可以阻止恶意执行sql注入</p>
<hr>
<h3 id="heading3">示例</h3>
<pre><code>CREATE DEFINER = `visitor`@`%` PROCEDURE `NewProc`()
BEGIN
-- 用户信息导入与总部绑定（将表 jpgk_member 和表 jpgk_member_ext 导入 jpgk_member_xinbao jpgk_member_org表）
-- 需要输入总部orgId 

declare uid int default 0;
declare status int default 1;
declare role int default 0;
declare companyName varchar(255) default 0;
declare orgId int default 0;
declare total int default 0;


declare error varchar(255) default 'error:';

	-- 创建结束标志变量 
  declare done int default false;   
	-- 创建游标
  declare cur cursor for 
		SELECT
		m.uid,
		m.`status`,
		m.role,
		c.company_name
		FROM
		jpgk_member m
		LEFT JOIN 
		jpgk_company c ON(m.cid=c.company_id AND c.company_id&gt;0);

  -- 指定游标循环结束时的返回值 
	declare continue HANDLER for not found set done = true;

  -- 打开游标
  open cur; 
    -- 开始循环游标里的数据  
    read_loop:loop  
    -- 根据游标当前指向的一条数据  
    fetch cur into uid,role,`status`,companyName;
		    -- 判断游标的循环是否结束  
    if done then  
        leave read_loop; -- 跳出游标循环  
    end if;
    -- 保存数据(SELECT INTO )
			
		BEGIN
			-- 捕获错误
			DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set error=CONCAT(error,orgId,',');
			set orgId=(SELECT org_id FROM jpgk_org WHERE org_type=2 and org_alias=companyName);
			IF orgId&gt;0 THEN
			set total=total+1;
			INSERT INTO jpgk_member_org(uid,org_id,`status`,`level`,role)VALUES(uid,orgId,`status`,3,role);
			END IF;
		END;
		
		set orgId=0;

    -- 结束游标循环  
    end loop;
  -- 关闭游标  
  close cur; 
	-- 输出结果
	-- select total,uid,companyName;
	SELECT total,error;

END;

</code></pre>
</div>


  
</article>
      <footer id="main-footer" class="container main_footer">
  

  <div class="container nav foot no-print">
  
<a href="https://lvzongcheng.gitee.io/license">license</a>


  <a class="toplink" href="#">back to top</a>

</div>

  <div class="container credits">
  
<div class="container footline">
  
  code with <!-- raw HTML omitted --><!-- raw HTML omitted -->


</div>


  
<div class="container copyright">
  
  (c) 2015 yourname.


</div>


</div>

</footer>

    </main>
    
<script type="text/javascript">
  (function() {
    
    
    if (window.location.hostname == "localhost")
      return;
    var dsq = document.createElement('script'); dsq.async = true; dsq.type = 'text/javascript';
    dsq.src = '//your_disqus_shortname.disqus.com/count.js';
    (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
  })();
</script>



<script src="/js/highlight.pack.js"></script>
<script>hljs.initHighlightingOnLoad();</script>



    
  </body>
</html>

